from openpyxl import load_workbook
from tqdm import tqdm

from QuickTools import tools_pmc
from QuickTools.tools_pmc import (today_str1, yesterday_str1, lj_gdwcqk_y)


def vlookup(lookup_value, table_array, col_index_num):
    global a
    for row in table_array:
        if row[0].value == lookup_value:
            a = table_array.index(row) - 1
            return row[col_index_num - 1].value


wb_bom = load_workbook(tools_pmc.lj_bom)
ws_bom = wb_bom.worksheets[0]

wb_gdwcqk = load_workbook(lj_gdwcqk_y)
ws_gdwcqk = wb_gdwcqk.worksheets[0]
wb = load_workbook(
    "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/config/库存明细账底表/库存明细账" + yesterday_str1 + '.xlsx')
ws = wb.active

# 移植工单完成表处理程序
ws_gdwcqk.insert_cols(3)
for i in range(2, ws_gdwcqk.max_row + 1):
    ws_gdwcqk.cell(i, 3).value = ws_gdwcqk.cell(i, 1).value + "-" + ws_gdwcqk.cell(i, 2).value

table_array = list(ws_gdwcqk['C1':'H' + str(ws_gdwcqk.max_row)])
for i in tqdm(range(2, ws.max_row + 1), desc="写入工单信息"):
    ws.cell(i, 5).value = tools_pmc.detect_date(ws.cell(i, 5).value, 1).date()
    ws.cell(i, 5).number_format = 'yyyy/mm/dd'
    global a
    a = 0
    if ws.cell(i, 14).value is not None:
        if len(ws.cell(i, 14).value) != 0 and ws.cell(i, 14).value[0] == "5":
            if vlookup(ws.cell(i, 14).value, table_array, 2)[0] == "3" \
                    or vlookup(ws.cell(i, 14).value, table_array, 2)[0] == "1":
                ws.cell(i, 16).value = vlookup(ws.cell(i, 14).value, table_array, 2)
                ws.cell(i, 17).value = vlookup(ws.cell(i, 14).value, table_array, 3)
                ws.cell(i, 18).value = vlookup(ws.cell(i, 14).value, table_array, 4)
                ws.cell(i, 19).value = vlookup(ws.cell(i, 14).value, table_array, 6)
            else:
                while table_array[a][1].value[0] != '3':
                    a -= 1
                sn = table_array[a][1].value
                if ws.cell(i, 1).value in tools_pmc.bom_1(sn, ws_bom)[0] or ws.cell(i, 1).value in tools_pmc.bom_1(sn, ws_bom)[2]:
                    ws.cell(i, 16).value = table_array[a][1].value
                    ws.cell(i, 17).value = table_array[a][2].value
                    ws.cell(i, 18).value = table_array[a][3].value
                    ws.cell(i, 19).value = table_array[a][5].value
                else:
                    ws.cell(i, 16).value = "BOM检测未通过，请手动查询！"


# for i in tqdm(range(ws.max_row, 1, -1), desc="删除无用行："):
#     if ws.cell(i, 5).value is None or len(ws.cell(i, 5).value) == 0:
#         ws.delete_rows(i)

wb.save(
    "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/config/库存明细账底表/库存明细账" + today_str1 + '.xlsx')
